﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;

namespace HRdex_Final.Forms
{
    public partial class WebForm1 : System.Web.UI.Page
    {
        string SessionID()
        {
            return Session["EmployeeID"].ToString();
        }
        protected void Page_Load(object sender, EventArgs e)
        {

            pFlexiForm.Visible = false;
            pNoA.Visible = false;
            pSubstitution.Visible = false;
            pCBAlist.Visible = false;
            pDownload.Visible = false;
            pLeave.Visible = true;

            SqlConnection conn = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\HRdexDB.mdf;Integrated Security=True;User Instance=True");
            SqlCommand cmdAdmin = new SqlCommand("SELECT formEmployees.EmployeeID, catUserLevel.catUserLevelName AS [User Level], catPositions.catPositionsName AS Position, catDepartment.DepartmentName AS Department, catCollege.CollegeName AS Office FROM formEmployees INNER JOIN catDepartment ON formEmployees.DeptID = catDepartment.DeptID INNER JOIN catCollege ON formEmployees.CollegeID = catCollege.CollegeID INNER JOIN catPositions ON formEmployees.catPositionsID = catPositions.catPositionsID INNER JOIN catUserLevel ON formEmployees.UserLevelID = catUserLevel.UserLevelID WHERE formEmployees.EmployeeID = " + SessionID(), conn);
            SqlDataAdapter daAdmin = new SqlDataAdapter(cmdAdmin);
            DataTable dtAdmin = new DataTable("dtAdmin");
            daAdmin.Fill(dtAdmin);

            if (approvepos(SessionID()) == "Head" || approvepos(SessionID()) == "Dean" || approvepos(SessionID()) == "Chairperson" || approvepos(SessionID()) == "Director")
            {
                //int totalapprove = livnotif(SessionID(), approvepos(SessionID())) + subnotif(SessionID(), approvepos(SessionID())) + flexnotif(SessionID(), approvepos(SessionID())) + cbanotif(SessionID(), approvepos(SessionID()));
                //AdminNavigation.Items[7].Text = "Forms(" + totalapprove + ")";
                btnCBA.Text = "CBA(" + cbanotif(SessionID(), approvepos(SessionID())) + ")";
                btnFlexi.Text = "Flexi(" + flexnotif(SessionID(), approvepos(SessionID())) + ")";
                btnLeave.Text = "Leave(" + livnotif(SessionID(), approvepos(SessionID())) + ")";
                btnSubstitution.Text = "Substitution(" + subnotif(SessionID(), approvepos(SessionID())) + ")";
            } 


            if (dtAdmin.Rows[0].ItemArray[1].ToString() == "Admin")
            {
                listLeave.SelectCommand = "SELECT formLeave.LeaveID, formEmployees.LastName + ', ' + formEmployees.FirstName + ' ' + formEmployees.MiddleName AS Name, formLeave.fromDateTime AS [From], formLeave.toDateTime AS [To], formLeave.HeadApproval AS [Head Approval], formLeave.DeanApproval AS [Dean/Director Approval], formLeave.HRApproval AS [HR Approval] FROM formLeave INNER JOIN formEmployees ON formLeave.EmployeeID = formEmployees.EmployeeID";
                listNoA.SelectCommand = "SELECT formNoticeofAbsence.NoticeOfAbsenceID, formEmployees.LastName + ', ' + formEmployees.FirstName + ' ' + formEmployees.MiddleName AS Name, catDepartment.DepartmentName AS Department, formNoticeofAbsence.pubDateReturn AS [Published Date], formNoticeofAbsence.FacApproval AS Approval FROM formNoticeofAbsence INNER JOIN formEmployees ON formNoticeofAbsence.ForEmpID = formEmployees.EmployeeID INNER JOIN catDepartment ON formEmployees.DeptID = catDepartment.DeptID ";
                FlexiForm.SelectCommand = "SELECT formFlexi.FlexiID, formEmployees.LastName + ', ' + formEmployees.FirstName + ' ' + formEmployees.MiddleName AS Name, formFlexi.OriginalDate AS [Original Date], formFlexi.OriginalTime AS [Original Time], formFlexi.FlexiDate AS [Flexi Date], formFlexi.FlexiTime AS [Flexi Time], formFlexi.pubDate AS [Published Date], formFlexi.HeadApproval AS [Head Approval], formFlexi.DeanApproval AS [Dean/Director Approval], formFlexi.HRApproval AS [HR Approval] FROM formFlexi INNER JOIN formEmployees ON formFlexi.EmpID = formEmployees.EmployeeID AND formFlexi.EmpID = formEmployees.EmployeeID";
                cba.SelectCommand = "SELECT formCBA.formCBAID, formCBA.EmployeeID AS [Employee ID], formEmployees.LastName + ', ' + formEmployees.FirstName + ' ' + formEmployees.MiddleName AS [Employee Name], formCBA.StudentNo AS [Student Number], formCBA.StudentName AS Beneficiary, formCBA.HRApproval AS [HR Approval] FROM formCBA INNER JOIN formEmployees ON formCBA.EmployeeID = formEmployees.EmployeeID";
                listSubstitution.SelectCommand = "SELECT formSubstitution.SubstitutionID, formEmployees.LastName + ', ' + formEmployees.FirstName + ' ' + formEmployees.MiddleName AS Name, catDepartment.DepartmentName AS Department, formSubstitution.HeadApproval AS [Head Approval], formSubstitution.DeanApproval AS [Dean/Director Approval], formSubstitution.HRApproval AS [HR Approval] FROM formSubstitution INNER JOIN formEmployees ON formSubstitution.EmpID = formEmployees.EmployeeID INNER JOIN catDepartment ON formSubstitution.DeptID = catDepartment.DeptID AND formEmployees.DeptID = catDepartment.DeptID";
            }
            else if (dtAdmin.Rows[0].ItemArray[1].ToString() == "SubAdmin" && dtAdmin.Rows[0].ItemArray[2].ToString() == "Head" && dtAdmin.Rows[0].ItemArray[3].ToString() == "HR Salaries and Benefits" )
            {
                listLeave.SelectCommand = "SELECT formLeave.LeaveID, formEmployees.LastName + ', ' + formEmployees.FirstName + ' ' + formEmployees.MiddleName AS Name, formLeave.fromDateTime AS [From], formLeave.toDateTime AS [To], formLeave.HeadApproval AS [Head Approval], formLeave.DeanApproval AS [Dean/Director Approval], formLeave.HRApproval AS [HR Approval] FROM formLeave INNER JOIN formEmployees ON formLeave.EmployeeID = formEmployees.EmployeeID";
                listNoA.SelectCommand = "SELECT formNoticeofAbsence.NoticeOfAbsenceID, formEmployees.LastName + ', ' + formEmployees.FirstName + ' ' + formEmployees.MiddleName AS Name, catDepartment.DepartmentName AS Department, formNoticeofAbsence.pubDateReturn AS [Published Date], formNoticeofAbsence.FacApproval AS Approval FROM formNoticeofAbsence INNER JOIN formEmployees ON formNoticeofAbsence.ForEmpID = formEmployees.EmployeeID INNER JOIN catDepartment ON formEmployees.DeptID = catDepartment.DeptID ";
                cba.SelectCommand = "SELECT formCBA.formCBAID, formCBA.EmployeeID AS [Employee ID], formEmployees.LastName + ', ' + formEmployees.FirstName + ' ' + formEmployees.MiddleName AS [Employee Name], formCBA.StudentNo AS [Student Number], formCBA.StudentName AS Beneficiary, formCBA.HRApproval AS [HR Approval] FROM formCBA INNER JOIN formEmployees ON formCBA.EmployeeID = formEmployees.EmployeeID";
                listSubstitution.SelectCommand = "SELECT formSubstitution.SubstitutionID, formEmployees.LastName + ', ' + formEmployees.FirstName + ' ' + formEmployees.MiddleName AS Name, catDepartment.DepartmentName AS Department, formSubstitution.HeadApproval AS [Head Approval], formSubstitution.DeanApproval AS [Dean/Director Approval], formSubstitution.HRApproval AS [HR Approval] FROM formSubstitution INNER JOIN formEmployees ON formSubstitution.EmpID = formEmployees.EmployeeID INNER JOIN catDepartment ON formSubstitution.DeptID = catDepartment.DeptID AND formEmployees.DeptID = catDepartment.DeptID";
            }
            else if (dtAdmin.Rows[0].ItemArray[1].ToString() == "SubAdmin" && dtAdmin.Rows[0].ItemArray[2].ToString() == "Head" && dtAdmin.Rows[0].ItemArray[3].ToString() == "HR Records and Info")
            {
                listSubstitution.SelectCommand = "SELECT formSubstitution.SubstitutionID, formEmployees.LastName + ', ' + formEmployees.FirstName + ' ' + formEmployees.MiddleName AS Name, catDepartment.DepartmentName AS Department, formSubstitution.ApprovalHead, formSubstitution.ApprovalDean, formSubstitution.ApprovalHR FROM formSubstitution INNER JOIN formEmployees ON formSubstitution.EmpID = formEmployees.EmployeeID INNER JOIN catDepartment ON formSubstitution.DeptID = catDepartment.DeptID AND formEmployees.DeptID = catDepartment.DeptID";
                FlexiForm.SelectCommand = "SELECT formFlexi.FlexiID, formEmployees.LastName + ', ' + formEmployees.FirstName + ' ' + formEmployees.MiddleName AS Name, formFlexi.OriginalDate AS [Original Date], formFlexi.OriginalTime AS [Original Time], formFlexi.FlexiDate AS [Flexi Date], formFlexi.FlexiTime AS [Flexi Time], formFlexi.pubDate AS [Published Date], formFlexi.HeadApproval AS [Head Approval], formFlexi.DeanApproval AS [Dean/Director Approval], formFlexi.HRApproval AS [HR Approval] FROM formFlexi INNER JOIN formEmployees ON formFlexi.EmpID = formEmployees.EmployeeID AND formFlexi.EmpID = formEmployees.EmployeeID";
                listNoA.SelectCommand = "SELECT formNoticeofAbsence.NoticeOfAbsenceID, formEmployees.LastName + ', ' + formEmployees.FirstName + ' ' + formEmployees.MiddleName AS Name, catDepartment.DepartmentName AS Department, formNoticeofAbsence.pubDateReturn AS [Published Date], formNoticeofAbsence.FacApproval AS Approval FROM formNoticeofAbsence INNER JOIN formEmployees ON formNoticeofAbsence.ForEmpID = formEmployees.EmployeeID INNER JOIN catDepartment ON formEmployees.DeptID = catDepartment.DeptID ";
            }
            else if (dtAdmin.Rows[0].ItemArray[1].ToString() == "Employee")
            {
                if (dtAdmin.Rows[0].ItemArray[2].ToString() == "Director" || dtAdmin.Rows[0].ItemArray[2].ToString() == "Head" || dtAdmin.Rows[0].ItemArray[2].ToString() == "Supervisor")
                {
                    listLeave.SelectCommand = "SELECT formLeave.LeaveID, formEmployees.LastName + ', ' + formEmployees.FirstName + ' ' + formEmployees.MiddleName AS Name, formLeave.fromDateTime AS [From], formLeave.toDateTime AS [To], formLeave.HeadApproval AS [Head Approval], formLeave.DeanApproval AS [Dean/Director Approval], formLeave.HRApproval AS [HR Approval] , catCollege.CollegeName, catDepartment.DepartmentName FROM formLeave INNER JOIN formEmployees ON formLeave.EmployeeID = formEmployees.EmployeeID INNER JOIN catCollege ON formLeave.CollegeID = catCollege.CollegeID AND formEmployees.CollegeID = catCollege.CollegeID INNER JOIN catDepartment ON formLeave.DepartmentID = catDepartment.DeptID AND formEmployees.DeptID = catDepartment.DeptID WHERE catCollege.CollegeName = '" + dtAdmin.Rows[0].ItemArray[4].ToString() + "'OR catDepartment.DepartmentName = '" + dtAdmin.Rows[0].ItemArray[3].ToString() + "'";
                    FlexiForm.SelectCommand = "SELECT formFlexi.FlexiID, formEmployees.LastName + ', ' + formEmployees.FirstName + ' ' + formEmployees.MiddleName AS Name, formFlexi.OriginalDate AS [Original Date], formFlexi.OriginalTime AS [Original Time], formFlexi.FlexiDate AS [Flexi Date], formFlexi.FlexiTime AS [Flexi Time], catDepartment.DepartmentName, catCollege.CollegeName, formFlexi.pubDate AS [Published Date], formFlexi.HeadApproval AS [Head Approval], formFlexi.DeanApproval AS [Dean/Director Approval], formFlexi.HRApproval AS [HR Approval] FROM formFlexi INNER JOIN formEmployees ON formFlexi.EmpID = formEmployees.EmployeeID AND formFlexi.EmpID = formEmployees.EmployeeID INNER JOIN catCollege ON formEmployees.CollegeID = catCollege.CollegeID INNER JOIN catDepartment ON formEmployees.DeptID = catDepartment.DeptID WHERE catCollege.CollegeName = '" + dtAdmin.Rows[0].ItemArray[4].ToString() + "'OR catDepartment.DepartmentName = '" + dtAdmin.Rows[0].ItemArray[3].ToString() + "'";
                    btnNoA.Enabled = false;
                    btnSubstitution.Enabled = false;
                }
            }
            else if (dtAdmin.Rows[0].ItemArray[1].ToString() == "Faculty")
            {
                if (dtAdmin.Rows[0].ItemArray[2].ToString() == "Dean" || dtAdmin.Rows[0].ItemArray[2].ToString() == "Chairperson" || dtAdmin.Rows[0].ItemArray[2].ToString() == "Head")
                {
                    listLeave.SelectCommand = "SELECT formLeave.LeaveID, formEmployees.LastName + ', ' + formEmployees.FirstName + ' ' + formEmployees.MiddleName AS Name, formLeave.fromDateTime AS [From], formLeave.toDateTime AS [To], formLeave.HeadApproval AS [Head Approval], formLeave.DeanApproval AS [Dean/Director Approval], formLeave.HRApproval AS [HR Approval] , catCollege.CollegeName, catDepartment.DepartmentName FROM formLeave INNER JOIN formEmployees ON formLeave.EmployeeID = formEmployees.EmployeeID INNER JOIN catCollege ON formLeave.CollegeID = catCollege.CollegeID AND formEmployees.CollegeID = catCollege.CollegeID INNER JOIN catDepartment ON formLeave.DepartmentID = catDepartment.DeptID AND formEmployees.DeptID = catDepartment.DeptID WHERE catCollege.CollegeName = '" + dtAdmin.Rows[0].ItemArray[4].ToString() + "'OR catDepartment.DepartmentName = '" + dtAdmin.Rows[0].ItemArray[3].ToString() + "'";
                    listNoA.SelectCommand = "SELECT formNoticeofAbsence.NoticeOfAbsenceID, formEmployees.LastName + ', ' + formEmployees.FirstName + ' ' + formEmployees.MiddleName AS Name, catDepartment.DepartmentName AS Department, formNoticeofAbsence.pubDateReturn AS [Published Date], formNoticeofAbsence.FacApproval AS Approval FROM formNoticeofAbsence INNER JOIN formEmployees ON formNoticeofAbsence.ForEmpID = formEmployees.EmployeeID INNER JOIN catDepartment ON formEmployees.DeptID = catDepartment.DeptID WHERE  catDepartment.DepartmentName = '" + dtAdmin.Rows[0].ItemArray[3].ToString() + "'";
                    listSubstitution.SelectCommand = "SELECT formSubstitution.SubstitutionID, formEmployees.LastName + ', ' + formEmployees.FirstName + ' ' + formEmployees.MiddleName AS Name, catDepartment.DepartmentName AS Department, formSubstitution.HeadApproval AS [Head Approval], formSubstitution.DeanApproval AS [Dean/Director Approval], formSubstitution.HRApproval AS [HR Approval] FROM formSubstitution INNER JOIN formEmployees ON formSubstitution.EmpID = formEmployees.EmployeeID INNER JOIN catDepartment ON formSubstitution.DeptID = catDepartment.DeptID INNER JOIN catCollege ON formSubstitution.CollegeID = catCollege.CollegeID WHERE catCollege.CollegeName = '" + dtAdmin.Rows[0].ItemArray[4].ToString() + "'OR catDepartment.DepartmentName = '" + dtAdmin.Rows[0].ItemArray[3].ToString() + "'";
                    btnFlexi.Enabled = false;
                }
            }

        }

        protected void btnLeave_Click(object sender, EventArgs e)
        {
            pFlexiForm.Visible = false;
            pNoA.Visible = false;
            pSubstitution.Visible = false;
            pCBAlist.Visible = false;
            pDownload.Visible = false;
            pLeave.Visible = true;
        }

        protected void btnCBA_Click(object sender, EventArgs e)
        {
            pFlexiForm.Visible = false;
            pNoA.Visible = false;
            pSubstitution.Visible = false;
            pCBAlist.Visible = true;
            pDownload.Visible = false;
            pLeave.Visible = false;
        }

        protected void btnFlexi_Click(object sender, EventArgs e)
        {
            pFlexiForm.Visible = true;
            pNoA.Visible = false;
            pSubstitution.Visible = false;
            pCBAlist.Visible = false;
            pDownload.Visible = false;
            pLeave.Visible = false;
        }

        protected void btnSubstitution_Click(object sender, EventArgs e)
        {
            pFlexiForm.Visible = false;
            pNoA.Visible = false;
            pSubstitution.Visible = true;
            pCBAlist.Visible = false;
            pDownload.Visible = false;
            pLeave.Visible = false;
        }

        protected void btnNoA_Click(object sender, EventArgs e)
        {
            pFlexiForm.Visible = false;
            pNoA.Visible = true;
            pSubstitution.Visible = false;
            pCBAlist.Visible = false;
            pDownload.Visible = false;
            pLeave.Visible = false;
        }

        protected void btnDownload_Click(object sender, EventArgs e)
        {
            pFlexiForm.Visible = false;
            pNoA.Visible = false;
            pSubstitution.Visible = false;
            pCBAlist.Visible = false;
            pDownload.Visible = true;
            pLeave.Visible = false;
        }


        int subnotif(string empid, string usrlvl)
        {
            string sqlc;
            if (usrlvl == "Head" || usrlvl == "Chairperson")
            {
                sqlc = "SELECT SubstitutionID FROM formSubstitution WHERE (DeptID IN  (SELECT catDepartment.DeptID " +
                       "FROM catDepartment INNER JOIN formEmployees ON catDepartment.DeptID = formEmployees.DeptID " +
                        "WHERE (formEmployees.EmployeeID = " + empid + "))) AND (HeadApproval IS NULL)";
            }
            else if (usrlvl == "Dean" || usrlvl == "Director")
            {
                sqlc = "SELECT SubstitutionID FROM formSubstitution " +
                    "WHERE (CollegeID IN (SELECT catCollege.CollegeID FROM catCollege INNER JOIN " +
                    "formEmployees ON catCollege.CollegeID = formEmployees.CollegeID" +
                    " WHERE (formEmployees.EmployeeID = " + empid + "))) AND (DeanApproval IS NULL) AND (HeadApproval IS NOT NULL)";
            }
            else
            {
                sqlc = "SELECT SubstitutionID FROM formSubstitution WHERE (HRApproval IS NULL) AND (DeanApproval IS NOT NULL)";
            }


            SqlDataAdapter cmd = new SqlDataAdapter(sqlc, conn);

            DataTable dtbl = new DataTable();

            cmd.Fill(dtbl);

            return dtbl.Rows.Count;
        }

        int livnotif(string empid, string usrlvl)
        {
            string sqlc;
            if (usrlvl == "Head" || usrlvl == "Chairperson")
            {
                sqlc = "SELECT LeaveID FROM formLeave WHERE (DepartmentID IN  (SELECT catDepartment.DeptID " +
                       "FROM catDepartment INNER JOIN formEmployees ON catDepartment.DeptID = formEmployees.DeptID " +
                        "WHERE (formEmployees.EmployeeID = " + empid + "))) AND (HeadApproval IS NULL)";
            }
            else if (usrlvl == "Dean" || usrlvl == "Director")
            {
                sqlc = "SELECT  LeaveID FROM formLeave " +
                    "WHERE (CollegeID IN (SELECT catCollege.CollegeID FROM catCollege INNER JOIN " +
                    "formEmployees ON catCollege.CollegeID = formEmployees.CollegeID" +
                    " WHERE (formEmployees.EmployeeID = " + empid + "))) AND (DeanApproval IS NULL) AND (HeadApproval IS NOT NULL)";
            }
            else
            {
                sqlc = "SELECT  LeaveID FROM formLeave WHERE (HRApproval IS NULL) AND (DeanApproval IS NOT NULL)";
            }


            SqlDataAdapter cmd = new SqlDataAdapter(sqlc, conn);

            DataTable dtbl = new DataTable();

            cmd.Fill(dtbl);

            return dtbl.Rows.Count;
        }

        int cbanotif(string empid, string usrlvl)
        {
            string sqlc;

            sqlc = "SELECT  formCBAID FROM formCBA WHERE (HRApproval IS NULL)";



            SqlDataAdapter cmd = new SqlDataAdapter(sqlc, conn);

            DataTable dtbl = new DataTable();

            cmd.Fill(dtbl);

            return dtbl.Rows.Count;
        }

        int flexnotif(string empid, string usrlvl)
        {
            string sqlc;
            if (usrlvl == "Head" || usrlvl == "Chairperson")
            {
                sqlc = "SELECT FlexiID FROM formFlexi WHERE (CollegeID IN  (SELECT catCollege.CollegeID " +
                       "FROM catCollege INNER JOIN formEmployees ON catCollege.CollegeID = formEmployees.CollegeID " +
                        "WHERE (formEmployees.EmployeeID = " + empid + "))) AND (HeadApproval IS NULL)";
            }
            else if (usrlvl == "Dean" || usrlvl == "Director")
            {
                sqlc = "SELECT  FlexiID FROM formFlexi " +
                    "WHERE (CollegeID IN (SELECT catCollege.CollegeID FROM catCollege INNER JOIN " +
                    "formEmployees ON catCollege.CollegeID = formEmployees.CollegeID" +
                    " WHERE (formEmployees.EmployeeID = " + empid + "))) AND (DeanApproval IS NULL) AND (HeadApproval IS NOT NULL)";
            }
            else
            {
                sqlc = "SELECT  FlexiID FROM formFlexi WHERE (HRApproval IS NULL) AND (DeanApproval IS NOT NULL)";
            }


            SqlDataAdapter cmd = new SqlDataAdapter(sqlc, conn);

            DataTable dtbl = new DataTable();

            cmd.Fill(dtbl);

            return dtbl.Rows.Count;
        }

        string approvepos(string empid)
        {
            string scmd = "SELECT catPositions.catPositionsName FROM catPositions INNER JOIN " +
                    "formEmployees ON catPositions.catPositionsID = formEmployees.catPositionsID " +
                    "WHERE formEmployees.EmployeeID = " + empid;

            SqlDataAdapter cmd = new SqlDataAdapter(scmd, conn);

            DataTable dtbl = new DataTable();

            cmd.Fill(dtbl);

            return dtbl.Rows[0][0].ToString();
        }
    }
}